﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_FILE_Insert_Xml')
	BEGIN
		DROP Procedure usp_UPDMS_COM_FILE_Insert_Xml
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_FILE_Insert_Xml
**	Desc : 첨부파일을 저장,수정한다. - XML
**	Test Exec Query : 아래 참고
**	Called by : Com_Dac_UPDMS_COM_FILE.cs
**	Program ID : AnyWhere
**	Auth : 송시명
**	Date : 2013-06-13
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_FILE_Insert_Xml]
@activity_id int,
@activity_cd varchar(10),
@ls_xml nvarchar(max) = '<UPDMS></UPDMS>' --루트노드
AS

BEGIN

DECLARE @iXmlHandle int
EXEC sp_xml_preparedocument @iXmlHandle OUTPUT, @ls_xml

IF @activity_id > 0 AND @activity_cd <> ''
BEGIN
  --삭제
  IF EXISTS( SELECT File_Id FROM UPDMS_COM_FILE WITH(NOLOCK) WHERE Activity_Id = @activity_id AND Activity_Cd = @activity_cd )
  BEGIN
    DELETE FROM UPDMS_COM_FILE
     WHERE Activity_Id = @activity_id
       AND Activity_Cd = @activity_cd
  END

  --다시 저장
  INSERT INTO UPDMS_COM_FILE (	
         Activity_Id,
         Activity_Cd,
         File_Dir,
         File_Nm,
         File_Size,
         File_Ext,
         File_W_Size,
         File_H_Size,
         Create_User,
         Create_Dt
  )
  SELECT @activity_id,
         @activity_cd,
         File_Dir,
         File_Nm,
         File_Size,
         File_Ext,
         File_W_Size,
         File_H_Size,
         Create_User,
         GETDATE()
    FROM OPENXML(@iXmlHandle, '/UPDMS/UPDMS_COM_FILE', 2)
    WITH (
         File_Dir nvarchar(500),
         File_Nm nvarchar(500),
         File_Size int,
         File_Ext nvarchar(10),
         File_W_Size int,
         File_H_Size int,
         Create_User nvarchar(20)
       ) X
   WHERE X.File_Nm IS NOT NULL
     AND X.File_Size IS NOT NULL

  --메모리 해제
  EXEC sp_xml_removedocument @iXmlHandle

END

END --IF @activity_id > 0 AND @activity_cd <> ''

GO

/*
--Test Exec Query--

DECLARE @ls_xml nvarchar(max)
SET @ls_xml='<UPDMS><UPDMS_COM_FILE><Activity_Id>112</Activity_Id><Activity_Cd>Ref1012m</Activity_Cd><File_Dir></File_Dir><File_Nm><![CDATA[2013-05-21_174757.jpg]]></File_Nm><File_Size>103704</File_Size><File_Ext>jpg</File_Ext><File_W_Size></File_W_Size><File_H_Size></File_H_Size><Create_User>smstyle</Create_User></UPDMS_COM_FILE><UPDMS_COM_FILE><Activity_Id>112</Activity_Id><Activity_Cd>Ref1012m</Activity_Cd><File_Dir></File_Dir><File_Nm><![CDATA[ExportToExcel.zip]]></File_Nm><File_Size>1868885</File_Size><File_Ext>zip</File_Ext><File_W_Size></File_W_Size><File_H_Size></File_H_Size><Create_User>smstyle</Create_User></UPDMS_COM_FILE></UPDMS>'

EXEC usp_UPDMS_COM_FILE_Insert_Xml @ls_xml
*/